﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using QuanLyBanHang.VO;
using System.Windows.Forms;


namespace QuanLyBanHang.DAL
{
    public class NhanVien_DAL
    {
        dbConnection conn;
        public NhanVien_DAL()
        {
            conn = new dbConnection();
        }
        public LinkedList<NhanVien_VO> Show_NV()
        {
            LinkedList<NhanVien_VO> List_NV = new LinkedList<NhanVien_VO>();
            try
            {
                SqlCommand cmd = new SqlCommand();
                NhanVien_VO nv = new NhanVien_VO();
                DataTable dt = new DataTable();
                dt = conn.execSelectQueryNonParameter("select * from NHANVIEN");
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    nv.MaNV = int.Parse(dt.Rows[i][0].ToString().Trim());
                    nv.hoten = dt.Rows[i][1].ToString().Trim();
                    nv.luong = int.Parse(dt.Rows[i][2].ToString().Trim());
                    nv.diachi = dt.Rows[i][3].ToString().Trim();
                    nv.sodt = dt.Rows[i][4].ToString().Trim();
                    nv.taikhoan = dt.Rows[i][5].ToString().Trim();
                    nv.matkhau = dt.Rows[i][6].ToString().Trim();
                    nv.quyen = int.Parse(dt.Rows[i][7].ToString().Trim());
                    List_NV.AddLast(nv);
                    nv = new NhanVien_VO();
                }
            }
            catch
            {
                MessageBox.Show("dafa");
            }
            return List_NV;
        }
        public DataTable Select(string key)
        {
            string query = "select * from NHANVIEN where TenNV like '%'+@ten+'%'";
            SqlParameter[] p = new SqlParameter[1];
            p[0] = new SqlParameter("@ten", key);
            return conn.execSelectQuery(query, p);
        }
        public bool insert(string ten, int luong, string dc, string sodt, string tk, string mk, int quyen)
        {
            string query = "insert into NHANVIEN values(@ten,@luong,@diachi,@sodt,@tk,@mk,@quyen)";

            SqlParameter[] p = new SqlParameter[7];
            p[0] = new SqlParameter("@ten", ten);
            p[1] = new SqlParameter("@luong", luong);
            p[3] = new SqlParameter("@diachi", dc);
            p[2] = new SqlParameter("@sodt", sodt);
            p[4] = new SqlParameter("@tk", tk);
            p[5] = new SqlParameter("@mk", mk);
            p[6] = new SqlParameter("@quyen", quyen);
            return conn.execInsertQuery(query, p);
        }
        public bool update(int id, string ten, int luong, string dc, string sodt, string tk, string mk, int quyen)
        {
            string query = "update NHANVIEN set TenNV=@ten,Luong=@luong,DiaChi=@diachi,SoDT=@sodt,TaiKhoan=@tk,MatKhau=@mk,Quyen=@quyen where MaNV=@manv";
            SqlParameter[] p = new SqlParameter[8];
            p[0] = new SqlParameter("@ten", ten);
            p[1] = new SqlParameter("@luong", luong);
            p[3] = new SqlParameter("@diachi", dc);
            p[2] = new SqlParameter("@sodt", sodt);
            p[4] = new SqlParameter("@tk", tk);
            p[5] = new SqlParameter("@mk", mk);
            p[6] = new SqlParameter("@quyen", quyen);
            p[7] = new SqlParameter("@manv", id);
            return conn.execInsertQuery(query, p);
        }
        public bool delete(int id)
        {
            string query = "delete from NHANVIEN where MaNV =@manv";
            SqlParameter[] p = new SqlParameter[1];
            p[0] = new SqlParameter("@manv", id);
            return conn.execDeleteQuery(query, p);
        }
        public DataTable ValidateUser(string tk, string mk)
        {
            string query = "select * from NHANVIEN where TaiKhoan=@tk and MatKhau=@mk";
            SqlParameter[] p = new SqlParameter[2];
            p[0] = new SqlParameter("@tk", tk);
            p[1] = new SqlParameter("@mk", mk);
            return conn.execSelectQuery(query, p);
        }
        public DataTable getTK(string tk)
        {
            DataTable dt = new DataTable();
            string query = "select TaiKhoan from NHANVIEN where TaiKhoan = @tk";
            SqlParameter[] p = new SqlParameter[1];
            p[0] = new SqlParameter("@tk",tk);
            return conn.execSelectQuery(query,p);
        }
        public DataTable getTKfromID(int id)
        {
            DataTable dt = new DataTable();
            string query = "select TaiKhoan from NHANVIEN where MaNV = @id";
            SqlParameter[] p = new SqlParameter[1];
            p[0] = new SqlParameter("@id", id);
            return conn.execSelectQuery(query, p);
        }
    }
}
